<?php
	class StudentPaymentController extends Controller{
		private $result = array("actionStatus"=>0,"actionMessage"=>"","model"=>array(),"actionError"=>array());	
		public $mainMenu = "Finance";
		public $subMenu = "";
		public $title = "";
		public $loginUser = "";
		public function init() {
			$this->loginUser = LoginUser::getUser();
		}
		
		public function actionIndex(){
			$data= Array();
			
			$arrDept = MDepartment::model()->findAll("is_active = 1");
			
			$data["arrDept"] = $arrDept;
			$this->layout ="//layouts/ly_blank";
			$this->render('index',$data);
		}
		public function actionDetailList(){
			$department_id = Yii::app()->request->getParam("department_id","");
			$start_date = Yii::app()->request->getParam("start_date","01-Jan-2000");			
			$start_date = date_format(date_create_from_format( "d-M-Y", $start_date),"Y-m-d");
			$end_date=Yii::app()->request->getParam("end_date","01-Jan-2000");
			$end_date = date_format(date_create_from_format( "d-M-Y", $end_date),"Y-m-d");
			
			$arrPayment = MStudentHpayment::model()->with(array(
					"student" => array("alias"=>"a"),
			))->findAll("a.department_id = :deptId and t.payment_date >= :startDate and t.payment_date <= :endDate",array(
					":deptId"=>$department_id,
					":startDate"=>$start_date,
					":endDate"=>$end_date
			));
			$model = Array();
			
			foreach ($arrPayment as $payment){
				$tmp = Array();
				$tmp["id"] = $payment->id;
				$tmp["payment_no"] = $payment->payment_no;
				$tmp["payment_date"] = $payment->payment_date;
				$tmp["student_id"] = $payment->student_id;
				$tmp["sid"] = $payment->student->sid;
				$tmp["student_name"] = $payment->student->name;
				$tmp["receive_acc_id"] = $payment->receive_acc_id;
				$tmp["ttl_payment"] = $payment->ttl_payment;
				$tmp["notes"] = $payment->notes;
				$model[] = $tmp;				
			}
			
			$this->result["actionStatus"]=0;
			$this->result["model"]=$model;			
			$this->returnJSON($this->result);			
		}
		
		public function actionDetailListDetail(){
			$department_id = Yii::app()->request->getParam("department_id","");
			$start_date = Yii::app()->request->getParam("start_date","01-Jan-2000");
			$start_date = date_format(date_create_from_format( "d-M-Y", $start_date),"Y-m-d");
			$end_date=Yii::app()->request->getParam("end_date","01-Jan-2000");
			$end_date = date_format(date_create_from_format( "d-M-Y", $end_date),"Y-m-d");
			/*	
			$arrPayment = MStudentHpayment::model()->with(array(
					"student" => array("alias"=>"a"),
			))->findAll("a.department_id = :deptId and t.payment_date >= :startDate and t.payment_date <= :endDate",array(
					":deptId"=>$department_id,
					":startDate"=>$start_date,
					":endDate"=>$end_date
			));
			*/
			
			$criteria = new CDbCriteria();			
			$arrPayment = MStudentDpayment::model()->with(array(
					"studentPayment" => array("alias"=>"a"),
					"studentPayment.student" => array("alias"=>"b"),
			))->findAll("b.department_id = :deptId and a.payment_date >= :startDate and a.payment_date <= :endDate",array(
					":deptId"=>$department_id,
					":startDate"=>$start_date,
					":endDate"=>$end_date
					
			));
				
			
			
			$model = Array();
				
			foreach ($arrPayment as $payment){
				$tmp = Array();
				$tmp["id"] = $payment->id;
				$tmp["payment_no"] = $payment->studentPayment->payment_no;
				$tmp["payment_date"] = $payment->studentPayment->payment_date;
				$tmp["student_id"] = $payment->studentPayment->student_id;
				$tmp["sid"] = $payment->studentPayment->student->sid;
				$tmp["student_name"] = $payment->studentPayment->student->name;
				$tmp["receive_acc_id"] = $payment->studentPayment->receive_acc_id;
				$tmp["invoice_id"] = $payment->student_invoice_id;
				$tmp["invoice_no"] = $payment->studentInvoice->invoice_no;				
				$tmp["ttl_payment"] = $payment->total_payment;
				$tmp["notes"] = $payment->notes;
				$tmp["is_active"] = $payment->studentPayment->is_active;
				$model[] = $tmp;
			}
				
			$this->result["actionStatus"]=0;
			$this->result["model"]=$model;
			$this->returnJSON($this->result);
		}
		public function actionInvoiceList(){
			$student_id = Yii::app()->request->getParam("student_id","");
			$arrInvoice = MStudentHinvoice::model()->findAll("student_id = :studentId and (ttl_paid + ttl_cn) < (ttl_invoice-ttl_disc) and is_active = 1",array(":studentId"=>$student_id));
			$model = array();
			foreach ($arrInvoice as $inv){
				$tmp = array();
				$tmp["id"] = $inv->id;
				$tmp["invoice_no"] = $inv->invoice_no;
				$tmp["bulk_no"] = $inv->bulk_no;
				$tmp["invoice_date"] = $inv->invoice_date;
				$tmp["notes"] = $inv->notes;
				$tmp["total"] = ($inv->ttl_invoice - $inv->ttl_disc) - ($inv->ttl_paid + $inv->ttl_cn);
				$tmp["payment"] = $tmp["total"];				
				$model[] = $tmp;
			}
			$this->result["actionStatus"] =0;
			$this->result["model"] =$model;		
			$this->returnJSON($this->result);
			
		}
		
		public function actionForm(){
			$department_id = Yii::app()->request->getParam("department_id","");
			$invoice_id = Yii::app()->request->getParam("invoice_id",0);
			$op = Yii::app()->request->getParam("op","");
			$id = Yii::app()->request->getParam("id",0);
			$payment_no = "";
			$payment_date = date("d-M-Y");
			$student_id ="";
			$student_name="";
			$notes ="";
			$arrInvoice="[]";
			
			if($op == "add"){
				$payment_no = AutoNumber::getNewStudentPaymentNo();				
			}
			
			if($invoice_id != ""){
				$hInvoice = MStudentHinvoice::model()->findByPK($invoice_id);
				$student_id = $hInvoice->student_id;
				$student_name = $hInvoice->student->sid . " - " .$hInvoice->student->name;
				$arrInvoice = MStudentHinvoice::model()->findAll("student_id = :studentId and (ttl_paid + ttl_cn) < (ttl_invoice -ttl_disc) and is_active = 1",array(":studentId"=>$student_id));
				$arrTmp = array();
				foreach ($arrInvoice as $inv){
					$tmp = array();
					$tmp["id"] = $inv->id;
					$tmp["invoice_no"] = $inv->invoice_no;
					$tmp["bulk_no"] = $inv->bulk_no;
					$tmp["invoice_date"] = $inv->invoice_date;
					$tmp["notes"] = $inv->notes;
					$tmp["total"] = ($inv->ttl_invoice - $inv->ttl_disc) - ($inv->ttl_paid + $inv->ttl_cn);
					$tmp["payment"] = $tmp["total"];
					$arrTmp[] = $tmp;
				}
				$arrInvoice = json_encode($arrTmp);
			}
			
			$groupReceiveId = AppSetting::getValue("penerimaan_acc_id");
			$groupReceive = MCoa::model()->find("id =:accId",array(":accId"=>$groupReceiveId));
			$arrReceiveAcc = MCoa::model()->findAll("acc_code like :groupCode and is_active = 1 and is_leaf = 1",array(":groupCode"=>$groupReceive->acc_code."%" ));
			$receive_acc_id ="";
			if ($arrReceiveAcc != null){
				$tmp = $arrReceiveAcc[0];
				$receive_acc_id =$tmp->id;
			}
			
			$arrStudent = MStudent::model()->findAll("department_id = :deptId and is_active = 1",array(":deptId"=>$department_id));
			$acStudent = array();
			foreach ($arrStudent as $student){
				$tmp = array();
				$tmp["label"] = $student->sid ." - " . $student->name;
				$tmp["value"] = $student->sid ." - " . $student->name;
				$tmp["id"] = $student->id;
				$tmp["department_id"] = $student->department_id;
				$acStudent[] = $tmp;
			}
			$data["department_id"]=$department_id;
			$data["op"]=$op;
			$data["id"]=$id;
			$data["payment_no"]=$payment_no;
			$data["payment_date"]=$payment_date;
			$data["arrReceiveAcc"] = $arrReceiveAcc;
			$data["acStudent"]=CJSON::encode($acStudent);			
			$data["student_name"]=$student_name;
			$data["student_id"]=$student_id;
			$data["receive_acc_id"]=$receive_acc_id;
			$data["notes"]=$notes ;
			$data["arrInvoice"] = $arrInvoice;
			$data["invoice_id"] = $invoice_id;
			
			$this->layout ="//layouts/ly_blank";
			$this->render('form',$data);
		}
		
		public function actionFormBatch(){
			$data = array();
			$groupReceiveId = AppSetting::getValue("penerimaan_acc_id");
			$groupReceive = MCoa::model()->find("id =:accId",array(":accId"=>$groupReceiveId));
			$arrReceiveAcc = MCoa::model()->findAll("acc_code like :groupCode and is_active = 1 and is_leaf = 1",array(":groupCode"=>$groupReceive->acc_code."%" ));
			$receive_acc_id =AppSetting::getValue("bcava_receive_acc_id");
			$data["receive_acc_id"]=$receive_acc_id;
			$data["arrReceiveAcc"]=$arrReceiveAcc;			
			$this->layout ="//layouts/ly_blank";
			$this->render('formBatch',$data);
		}
		
		public function actionProcessBatch(){						
			if ($_FILES["fileBatch"]["error"] > 0) {
			  //echo "Error: " . $_FILES["fileBatch"]["error"] . "<br>";
			} else {
			  //echo "Upload: " . $_FILES["fileBatch"]["name"] . "<br>";
			  //echo "Type: " . $_FILES["fileBatch"]["type"] . "<br>";
			  //echo "Size: " . ($_FILES["fileBatch"]["size"] / 1024) . " kB<br>";
			  //echo "Stored in: " . $_FILES["fileBatch"]["tmp_name"];			  
			  $fileBatch = $_FILES["fileBatch"]["tmp_name"];
			  $receive_acc_id  = Yii::app()->request->getParam("receive_acc_id","");
			 
			  $notes = Yii::app()->request->getParam("notes","");
			  
			  
			  
			  $phpExcelPath = Yii::getPathOfAlias('ext.phpexcel.Classes');
			  require_once $phpExcelPath. DIRECTORY_SEPARATOR ."PHPExcel". DIRECTORY_SEPARATOR ."IOFactory.php";
			  $objPHPExcel = PHPExcel_IOFactory::load($fileBatch);

			  $objPHPExcel->setActiveSheetIndex(0);
			  $sheet = $objPHPExcel->getSheet(0);
			  $maxRow = $sheet->getHighestRow();
			  $maxCol = $sheet->getHighestColumn();
			  $rowDatas = $sheet->rangeToArray('A2:' .$maxCol.$maxRow,null,true,false);	
			  //print_r($rowDatas);
			  echo "</br>";
			  $conn = Yii::app()->db;
			  
			  
			  $arrData = array();
			  $allDataIsValid = true;
			  
			  foreach ($rowDatas as $rowData){
				$no = $rowData[0];
			  	$vaBCA = $rowData[1];
			  	$cur = $rowData[2];
			  	$amount = $rowData[3];
			  	$amount = Utils::parseCurrency($amount);
			  	$paymentDate = $rowData[4];
			  	$paymentDate = date_create_from_format("d/m/y H:i:s", $paymentDate);
			  	$paymentDate  = date_format($paymentDate,"Y-m-d H:i:s");
			  	$location = $rowData[5];
			  	$keterangan1 = $rowData[6];
			  	$keterangan2 = $rowData[7];
			  	//echo "$no $vaBCA $cur $amount $paymentDate $location $keterangan1 $keterangan2 </br>";
			  	
			  	$student = MStudent::model()->find("bca_va_no = :noVA",array(":noVA" => $vaBCA));
			  	
			  	$tmpArr = array();
			  	$tmpArr["no"] = $no;
			  	$tmpArr["bca_va_no"] = $vaBCA;
			  	$tmpArr["payment_date"] = $paymentDate;
			  	$tmpArr["keterangan"] = $location . " - " . $keterangan1 ." ".$keterangan2;
			  	$tmpArr["message"] ="";
			  				  				  	
			  	if($student){			  		
			  		$studentId = $student->id;
			  		$tmpArr["student_id"] = $studentId;			  		
			  		//get all outstanding invoice
			  		$arrHInv = MStudentHinvoice::model()->findAll("student_id = :studentId and payment_type = 'BCA VA' ",array(":studentId"=>$studentId));
			  		$totalOutInvoice =0;
			  		$arrTmpDetail = array();
			  		foreach ($arrHInv as $hInv){
			  			$tmpDetail = array();
			  			$tmpDetail["invoice_id"] = $hInv->id;
			  			$tmpDetail["total_invoice"] = $hInv->ttl_invoice;
			  			$tmpDetail["total_disc"] = $hInv->ttl_disc;
			  			$tmpDetail["total_paid"] = $hInv->ttl_paid;
			  			$tmpDetail["total_cn"] = $hInv->ttl_cn;
			  			$tmpDetail["total_out"] = $hInv->ttl_invoice - ($hInv->ttl_disc + $hInv->ttl_paid + $hInv->ttl_cn);
			  			
			  			$arrTmpDetail[] = $tmpDetail;			  			
			  			$totalOutInvoice += $tmpDetail["total_out"];			  			
			  		}
			  		$tmpArr["total_invoice"] = $totalOutInvoice;
			  		$tmpArr["detail_invoice"] = $arrTmpDetail;			  		
			  		if ($totalOutInvoice != $amount ){
			  			$tmpArr["message"] = "Payment $amount , not match with total invoice $totalOutInvoice";
			  			$allDataIsValid = false;
			  		}
			  		$tmpArr["total_paid"] = $amount;
			  	}else{
			  		$allDataIsValid = false;
			  		$tmpArr["message"] = $vaBCA . " is unregistered on database system";
			  	}			  	
			  	$arrData[] = $tmpArr;			  	
			  } //END foreach ($rowDatas as $rowData){
			  
			  if (!$allDataIsValid){
			  	//print_r($arrData);			 
			  	//foreach($arrData as $error){
			  	//	echo "<div>";
			  	//		print_r($error);
			  	//	echo "</div>";
			  	//} 
			  }else{
			  	//echo Yii::t("label","Data Pembayaran telah di proses !");
			  	$transaction = $conn->beginTransaction();
			  	try{
				  	foreach ($arrData as $bill){
				  		$paymentNo = AutoNumber::getNewStudentPaymentNo();
				  		
				  		$hJurnal = Jurnal::createHeader("Bulk Payment $paymentNo", $bill["payment_date"]);
				  		$jurnalId = $hJurnal["id"];
				  		
				  		$hPay = new MStudentHpayment();
				  		$hPay->payment_no =$paymentNo;
				  		$hPay->payment_date = $bill["payment_date"];
				  		$hPay->student_id = $bill["student_id"];
				  		
				  		$hPay->receive_acc_id = $receive_acc_id;
				  		$hPay->ttl_payment = $bill["total_invoice"];
				  		$hPay->notes = $notes . " - " . $bill["keterangan"];
				  		$hPay->jurnal_id = $jurnalId;
				  		$hPay->input_date_time = date("Y-m-d H:i:s");
				  		$hPay->user_id = $this->loginUser->id;
				  		$hPay->is_active = 1;				  		
				  		if (!$hPay->save()){
				  			throw new Exception(json_encode($hPay->getErrors()));
				  		}
				  		
				  		//detail paymnet
				  		$arrHInv = $bill["detail_invoice"];
				  		foreach($arrHInv as $hInv){
				  			$dPay = new MStudentDpayment();
				  			$dPay->student_payment_id = $hPay->id;
				  			$dPay->student_invoice_id = $hInv["invoice_id"];
				  			$dPay->total_invoice = $hInv["total_out"];
				  			$dPay->total_payment = $hInv["total_out"];
				  			$dPay->notes ="";
				  			if (!$dPay->save()){
				  				throw new Exception(json_encode($dPay->getErrors()));
				  			}				  			
				  			//update invoice
				  			$hInv = MStudentHinvoice::model()->findByPK($hInv["invoice_id"]);
				  			$hInv->ttl_paid = $dPay->total_payment;
				  			if (!$hInv->save()){
				  				throw new Exception(json_encode($hInv->getErrors()));
				  			}
				  			
				  			//Jurnal detail for piutang account position Credit
				  			$tmp = Jurnal::createDetail($jurnalId, $hInv->piutang_acc_id, "", "C",$hInv->ttl_paid);				  			
				  		}
				  		//detail jurnal payment
				  		$tmp = Jurnal::createDetail($jurnalId, $receive_acc_id, "", "D", $bill["total_invoice"]);				  						  		 	
				  	}
				  	
				  	$transaction->commit();
			  	}catch (Exception $e){
			  		$allDataIsValid = false;
			  		$arrData[] = $e->getMessage();
			  		$transaction->rollback();
			  	}
			  	
			  }
			}			
				
			$data = array();
			$groupReceiveId = AppSetting::getValue("penerimaan_acc_id");
			$groupReceive = MCoa::model()->find("id =:accId",array(":accId"=>$groupReceiveId));
			$arrReceiveAcc = MCoa::model()->findAll("acc_code like :groupCode and is_active = 1 and is_leaf = 1",array(":groupCode"=>$groupReceive->acc_code."%" ));
			$receive_acc_id =AppSetting::getValue("bcava_receive_acc_id");
			$data["receive_acc_id"]=$receive_acc_id;
			$data["arrReceiveAcc"]=$arrReceiveAcc;
			$data["allDataIsValid"] = $allDataIsValid;
			$data["errorLog"] = $arrData;
			$this->layout ="//layouts/ly_blank";
			$this->render('formBatch',$data);
		}
		
		public function actionEdit(){
			$errorMessage =Yii::t("label","Pembayaran gagal di simpan !");
			$op = Yii::app()->request->getParam("op","");
			$id = Yii::app()->request->getParam("id",0);
			
			if ($op == "add"){
				$payment_date =Yii::app()->request->getParam("payment_date","");
				if ($payment_date == ""){
					$this->pageError(Yii::t("label","data payment date tidak valid"));
				}else{
					$payment_date = date_format(date_create_from_format( "d-M-Y", $payment_date),"Y-m-d");
				}
				$student_id = Yii::app()->request->getParam("student_id","");
				$receive_acc_id = Yii::app()->request->getParam("receive_acc_id","");
				$ttl_payment = Yii::app()->request->getParam("ttl_payment",0);
				$ttl_payment = Utils::parseCurrency($ttl_payment);
				$notes = Yii::app()->request->getParam("notes","");
				$strArrPaidInvoice = Yii::app()->request->getParam("arrPaidInvoice","[]");
				$arrPaidInvoice = json_decode($strArrPaidInvoice);
				$conn = Yii::app()->db;
				$transaction = $conn->beginTransaction();				
				try{
					$payment_no = AutoNumber::getNewStudentPaymentNo();
					
					//Jurnal Payment
					$tmp = Jurnal::createHeader("Payment $payment_no ", $payment_date);
					$jurnalId = $tmp["id"];
					
					//payment header
					$hPayment = new MStudentHpayment();					
					$hPayment->payment_no = $payment_no;
					$hPayment->payment_date =$payment_date;
					$hPayment->student_id = $student_id;
					$hPayment->receive_acc_id = $receive_acc_id;
					$hPayment->ttl_payment = $ttl_payment;
					$hPayment->notes= $notes;
					$hPayment->jurnal_id = $jurnalId;
					$hPayment->input_date_time= date("Y-m-d H:i:s");
					$loginUser = LoginUser::getUser();
					$hPayment->user_id= $loginUser->id;
					$hPayment->is_active = 1;
					if(!$hPayment->save()){
						throw new Exception(json_encode($hPayment->getErrors));
					}
					
					$ttlPayment =0;
					//payment Detail
					foreach ($arrPaidInvoice as $paidInvoice){
						$dPayment = new MStudentDpayment();
						$dPayment->student_payment_id = $hPayment->id;
						$dPayment->student_invoice_id = $paidInvoice->id;
						$dPayment->total_invoice = $paidInvoice->total;
						$dPayment->total_payment = $paidInvoice->payment;
						$dPayment->notes = $paidInvoice->notes;
						
						$ttlPayment += $paidInvoice->payment;
						
						if(!$dPayment->save()){
							throw new Exception(json_encode($dPayment->getErrors));
						}
						//Update Invoice header
						$hInvoice = MStudentHinvoice::model()->findByPK($paidInvoice->id);
						if ($hInvoice == null){
							throw new Exception("Invalid invoice ID");
						}
						
						$hInvoice->ttl_paid = $hInvoice->ttl_paid + $paidInvoice->payment;
						if(!$hInvoice->save()){
							throw new Exception(json_encode($hInvoice->getErrors()));
							
						}
						//Jurnal detail for piutang account position Credit 
						$tmp = Jurnal::createDetail($jurnalId, $hInvoice->piutang_acc_id, "", "C",$hInvoice->ttl_paid);	
					}
					//create detail Jurnal					
					$tmp = Jurnal::createDetail($jurnalId, $receive_acc_id, "", "D", $ttlPayment);
				}catch (Exception $e){
					$transaction->rollback();
					$this->pageError($errorMessage, $e->getMessage(),$e);					
				}				
				$transaction->commit();				
				$this->redirect(array('keuangan/tu/studentPayment/paymentReceipt','id'=>$hPayment->id));				
			}else if($op == "delete"){
				$errorMessage = Yii::t("dialog","Data pembayaran gagal di hapus!");
				try{
					$studentPayment = MStudentHpayment::model()->find("id =:id",array(":id"=>$id));
					if ($studentPayment == null){
						$errorMessage = Yii::t("dialog","Data Pembayaran tidak di temukan !");
						throw new Exception("Invalid payment id");
					}
					$conn = Yii::app()->db;
					$transaction = $conn->beginTransaction();
					$jurnalId = $studentPayment->jurnal_id;
					$payment_no = $studentPayment->payment_no;

					// delete detail jurnal & detail Payment
					Jurnal::deleteDetailJurnal($jurnalId);
					$arrDPayment = MStudentDpayment::model()->findAll("student_payment_id = ".$studentPayment->id);
					foreach ($arrDPayment as $dPayment){
						//update invoice
						$studentInv = MStudentHinvoice::model()->find("id = " .$dPayment->student_invoice_id);
						if ($studentInv != null){
							$studentInv->ttl_paid = $studentInv->ttl_paid - $dPayment->total_payment;
							if (!$studentInv->save()){
								throw new Exception(json_encode($studentInv->getErrors()));
							}														
						}
					}
					$ssql = "delete from m_student_dpayment where student_payment_id = ".$studentPayment->id;
					$cmd = $conn->createCommand($ssql);
					$cmd->execute();
					
					// delete header payment & header jurnal
					if(!$studentPayment->delete()){
						throw new Exception(json_encode($studentPayment->getErrors()));
					}
					Jurnal::deleteHeaderJurnal($jurnalId,$conn);
										
					$transaction->commit();
					$successMessage = Yii::t("dialog",'Pembayaran $payment_no telah di hapus !');
					$successMessage = str_replace('$payment_no', $payment_no, $successMessage);						
					$this->result["actionStatus"] = 0;
					$this->result["actionMessage"] = $successMessage;  
					$this->result["actionError"] = "";
					$this->returnJSON($this->result);					
				}catch (Exception $e){
					$transaction->rollback();
					$this->result["actionStatus"] = 1;
					$this->result["actionMessage"] = Yii::t("dialog",$errorMessage);
					$this->result["actionError"] = $e->getMessage();
					$this->returnJSON($this->result);
				}
			}						
		}
		
		public function actionPaymentReceipt(){
			$errorMessage = Yii::t("label","Data pembayaran tidak di temukan !");
			$id = Yii::app()->request->getParam("id","");
			$hPayment = MStudentHpayment::model()->find("id =:paymentId and is_active = 1",array(":paymentId"=>$id));
			
			if ($hPayment == null){
				$this->pageError($errorMessage,"Payment id not found");
			}
			
			$arrDPayment = MStudentDpayment::model()->findAll("student_payment_id =:paymentId",array(":paymentId"=>$id));
			$data["id"] = $id;
			$data["hPayment"] = $hPayment;
			$data["arrDPayment"] = $arrDPayment;
			
			$this->layout ="//layouts/ly_blank";
			$this->render('paymentReceipt',$data);			
		}
		
		public function actionPaymentReceiptAsExcel(){
			
			//prepare data
			$errorMessage = Yii::t("label","Data pembayaran tidak di temukan !");
			$id = Yii::app()->request->getParam("id","");
			$hPayment = MStudentHpayment::model()->find("id =:paymentId and is_active = 1",array(":paymentId"=>$id));
			
			if ($hPayment == null){
				$this->pageError($errorMessage,"Payment id not found");
			}
			
			$arrDPayment = MStudentDpayment::model()->findAll("student_payment_id =:paymentId",array(":paymentId"=>$id));
			$data["id"] = $id;
			$data["hPayment"] = $hPayment;
			$data["arrDPayment"] = $arrDPayment;
			
			//prepare Excel Object
			Yii::import('ext.phpexcel.XPHPExcel');
			
			//$objPHPExcel= XPHPExcel::createPHPExcel();
			
			
			$filetemplate = "studentPayment_paymentReceipt.xlsx";
			//$filetemplate = dirname(__FILE__) . DIRECTORY_SEPARATOR . "..". DIRECTORY_SEPARATOR ."exceltemplate". DIRECTORY_SEPARATOR ."keuangan". DIRECTORY_SEPARATOR ."tu".DIRECTORY_SEPARATOR .$filetemplate;
			$filetemplate =  Yii::app()->basePath .DIRECTORY_SEPARATOR . "exceltemplate". DIRECTORY_SEPARATOR ."keuangan". DIRECTORY_SEPARATOR ."tu".DIRECTORY_SEPARATOR .$filetemplate;
			//$filetemplate =  "E:\\test.xlsx";
			//echo $filetemplate ;
			
			$objPHPExcel = XPHPExcel::createPHPExcel();
			$objReader = PHPExcel_IOFactory::createReader('Excel2007');
			$objPHPExcel = $objReader->load($filetemplate);
			
			$objPHPExcel->getProperties()->setCreator("Raba Komunikatama")
			->setLastModifiedBy("Raba Komunikatama")
			->setTitle("Office 2007 XLSX Stundent Payment Receipt")
			->setSubject("Office 2007 XLSX Stundent Payment Receipt")
			->setDescription("Stundent Payment Receipt for Office 2007 XLSX, generated by Raba Komunikatama.")
			->setKeywords("office 2007 openxml php")
			->setCategory("");
				
 
			$objPHPExcel->setActiveSheetIndex(0);				
			$myWorkSheet = $objPHPExcel->getActiveSheet();
			// Add some data Header
			$myWorkSheet->setCellValue('C3', $hPayment->student->sid ." - " . $hPayment->student->name)
			->setCellValue('C4', $hPayment->receiveAcc->name)
			->setCellValue('C5', $hPayment->notes)
			->setCellValue('C6', $hPayment->user->user_name)
			->setCellValue('F3', $hPayment->payment_no)
			->setCellValue('F4', date_format(date_create_from_format( "Y-m-d", $hPayment->payment_date),"d-M-Y"));
			
			// Add detail data
			$grandTtl =0;
			$jmlDetail = sizeof($arrDPayment);
			
			//$objPHPExcel->getActiveSheet()->duplicateStyle($objPHPExcel->getActiveSheet()->getStyle('A8:G8'),'A'. $jmlDetail + 8 .':A' . $jmlDetail + 8);
			//$objPHPExcel->getActiveSheet()->duplicateStyle($objPHPExcel->getActiveSheet()->getStyle('A8:G8'),'A9:G13');
			
			$activeRow =8;
			foreach($arrDPayment as $dPayment){				
				
				$myWorkSheet->mergeCells('A'.$activeRow.':B'.$activeRow);
				
				//rata Kanan
				$myWorkSheet->getStyle("E$activeRow")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
				$myWorkSheet->getStyle("F$activeRow")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
				$myWorkSheet->getStyle("G$activeRow")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
				
				//format money
				$myWorkSheet->getStyle("E$activeRow:G$activeRow")->getNumberFormat()->setFormatCode("#,##0");
				
				//warp text
				$objPHPExcel->getActiveSheet()->getStyle("D$activeRow")->getAlignment()->setWrapText(true);
				
				$grandTtl += $dPayment->total_payment;
				$balance = $dPayment->total_invoice - $dPayment->total_payment;
				$myWorkSheet->setCellValue('A' . $activeRow,  $dPayment->studentInvoice->invoice_no)
				->setCellValue('C' . $activeRow,  date_format(date_create_from_format( "Y-m-d", $dPayment->studentInvoice->invoice_date),"d-M-Y"))
				->setCellValue('D' . $activeRow,  $dPayment->notes)
				->setCellValue('E' . $activeRow,  $dPayment->total_invoice)
				->setCellValue('F' . $activeRow,  $dPayment->total_payment)
				->setCellValue('G' . $activeRow,  $balance);
				
				//Utils::formatCurrency($balance));
				$activeRow++;
				$myWorkSheet->insertNewRowBefore($activeRow,1);
				
				//$objPHPExcel->getActiveSheet()->duplicateStyle($objPHPExcel->getActiveSheet()->getStyle('A8:G8'),'A'.$activeRow.':G'.$activeRow);				
			} 
			
			//footer
			$myWorkSheet->mergeCells('A'.$activeRow.':D'.$activeRow);
			$myWorkSheet->setCellValue('A' . $activeRow,  "##". Utils::terbilang($grandTtl) ."##");
			$myWorkSheet->mergeCells('E'.$activeRow.':G'.$activeRow);
			
			$myWorkSheet->setCellValue('E' . $activeRow,  "Total Payment: Rp. ". Utils::formatCurrency($grandTtl));
			$myWorkSheet->getStyle("E$activeRow")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
			// Rename worksheet
			$objPHPExcel->getActiveSheet()->setTitle('Student Payment Receipt');
				
			// Set active sheet index to the first sheet, so Excel opens this as the first sheet
			$objPHPExcel->setActiveSheetIndex(0);
			
			// Redirect output to a client’s web browser (Excel5)
			header('Content-Type: application/vnd.ms-excel');
			header('Content-Disposition: attachment;filename="StudentPaymentReceipt123456.xls"');
			header('Cache-Control: max-age=0');
			// If you're serving to IE 9, then the following may be needed
			header('Cache-Control: max-age=1');
				
			// If you're serving to IE over SSL, then the following may be needed
			header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
			header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
			header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
			header ('Pragma: public'); // HTTP/1.0
				
			$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
			$objWriter->save('php://output');
			Yii::app()->end();
			exit;
			
			
				
			$this->layout ="//layouts/ly_blank";
			$this->render('paymentReceipt',$data);			
		}
		
		protected function beforeRender($view){
			if ($this->subMenu != ""){
				$this->title = $this->mainMenu . " - " .$this->subMenu;
			}
			return true;
		}
		
	}
?>